﻿using sqlserverdemo.entitys;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace sqlserverdemo.ado
{

    public class DbHelper
    {
        private readonly string _connStr;

        public DbHelper(string connStr)
        {
            _connStr = connStr;
        }

        public int InsertUser(TUser user)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                string sql = "INSERT INTO tuser (name, age, mail, sex, addr, phone) VALUES (@name, @age, @mail, @sex, @addr, @phone)";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@name", user.Name);
                    cmd.Parameters.AddWithValue("@age", user.Age);
                    cmd.Parameters.AddWithValue("@mail", user.Mail);
                    cmd.Parameters.AddWithValue("@sex", user.Sex);
                    cmd.Parameters.AddWithValue("@addr", user.Addr);
                    cmd.Parameters.AddWithValue("@phone", user.Phone);

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public List<TUser> GetAllUsers()
        {
            var users = new List<TUser>();
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                string sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        users.Add(new TUser
                        {
                            Id = reader.GetInt32("id"),
                            Name = reader.GetString("name"),
                            Age = reader.GetInt32("age"),
                            Mail = reader.GetString("mail"),
                            Sex = reader.GetString("sex"),
                            Addr = reader.GetString("addr"),
                            Phone = reader.GetString("phone")
                        });
                    }
                }
            }
            return users;
        }

        // 更新用户信息
        public int UpdateUser(TUser user)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                string sql = "UPDATE tuser SET name=@name, age=@age, mail=@mail, sex=@sex, addr=@addr, phone=@phone WHERE id=@id";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@name", user.Name);
                    cmd.Parameters.AddWithValue("@age", user.Age);
                    cmd.Parameters.AddWithValue("@mail", user.Mail);
                    cmd.Parameters.AddWithValue("@sex", user.Sex);
                    cmd.Parameters.AddWithValue("@addr", user.Addr);
                    cmd.Parameters.AddWithValue("@phone", user.Phone);
                    cmd.Parameters.AddWithValue("@id", user.Id);

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 删除用户
        public int DeleteUser(int id)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                string sql = "DELETE FROM tuser WHERE id=@id";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 按条件查询（如按姓名）
        public List<TUser> GetUsersByName(string name)
        {
            var users = new List<TUser>();
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                string sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser WHERE name=@name";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@name", name);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            users.Add(new TUser
                            {
                                Id = reader.GetInt32("id"),
                                Name = reader.GetString("name"),
                                Age = reader.GetInt32("age"),
                                Mail = reader.GetString("mail"),
                                Sex = reader.GetString("sex"),
                                Addr = reader.GetString("addr"),
                                Phone = reader.GetString("phone")
                            });
                        }
                    }
                }
            }
            return users;
        }


        public int InsertUsers(List<TUser> users)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        string sql = "INSERT INTO tuser (name, age, mail, sex, addr, phone) VALUES (@name, @age, @mail, @sex, @addr, @phone)";
                        using (SqlCommand cmd = new SqlCommand(sql, conn, tran))
                        {
                            int total = 0;
                            foreach (var user in users)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@name", user.Name);
                                cmd.Parameters.AddWithValue("@age", user.Age);
                                cmd.Parameters.AddWithValue("@mail", user.Mail);
                                cmd.Parameters.AddWithValue("@sex", user.Sex);
                                cmd.Parameters.AddWithValue("@addr", user.Addr);
                                cmd.Parameters.AddWithValue("@phone", user.Phone);

                                total += cmd.ExecuteNonQuery();
                            }
                            tran.Commit();
                            return total;
                        }
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        public int InsertUsersByDataSet(List<TUser> users)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        string sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser WHERE 1=0"; // 只取结构
                        using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
                        {
                            adapter.SelectCommand.Transaction = tran;
                            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

                            DataSet ds = new DataSet();
                            adapter.Fill(ds, "tuser");
                            DataTable table = ds.Tables["tuser"];

                            foreach (var user in users)
                            {
                                DataRow row = table.NewRow();
                                // row["id"] 不需要赋值（自增）
                                row["name"] = user.Name;
                                row["age"] = user.Age;
                                row["mail"] = user.Mail;
                                row["sex"] = user.Sex;
                                row["addr"] = user.Addr;
                                row["phone"] = user.Phone;
                                table.Rows.Add(row);
                            }

                            int rows = adapter.Update(ds, "tuser");
                            tran.Commit();
                            return rows;
                        }
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        public DataTable GetAllUsersByDataSet()
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                string sql = "SELECT id, name, age, mail, sex, addr, phone FROM tuser";
                using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "tuser");
                    return ds.Tables["tuser"];
                }
            }
        }
    }

    public class GenericRepository<T> where T : class, new()
    {
        private readonly string _connStr;
        private readonly string _tableName;
        private readonly PropertyInfo[] _properties;

        public GenericRepository(string connStr, string tableName)
        {
            _connStr = connStr;
            _tableName = tableName;
            _properties = typeof(T).GetProperties();
        }

        // 插入
        public int Insert(T entity)
        {
            var columns = _properties.Where(p => p.Name.ToLower() != "id").Select(p => p.Name).ToArray();
            var values = columns.Select(c => "@" + c).ToArray();
            string sql = $"INSERT INTO {_tableName} ({string.Join(",", columns)}) VALUES ({string.Join(",", values)})";

            using (var conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (var cmd = new SqlCommand(sql, conn))
                {
                    foreach (var col in columns)
                    {
                        var value = typeof(T).GetProperty(col).GetValue(entity) ?? DBNull.Value;
                        cmd.Parameters.AddWithValue("@" + col, value);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 删除
        public int DeleteById(object id)
        {
            string sql = $"DELETE FROM {_tableName} WHERE id=@id";
            using (var conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (var cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 更新
        public int Update(T entity)
        {
            var columns = _properties.Where(p => p.Name.ToLower() != "id").Select(p => p.Name).ToArray();
            string setClause = string.Join(",", columns.Select(c => $"{c}=@{c}"));
            string sql = $"UPDATE {_tableName} SET {setClause} WHERE id=@id";

            using (var conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (var cmd = new SqlCommand(sql, conn))
                {
                    foreach (var col in columns)
                    {
                        var value = typeof(T).GetProperty(col).GetValue(entity) ?? DBNull.Value;
                        cmd.Parameters.AddWithValue("@" + col, value);
                    }
                    // 假设主键为Id
                    var idValue = typeof(T).GetProperty("Id").GetValue(entity);
                    cmd.Parameters.AddWithValue("@id", idValue);
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        // 查询所有
        public List<T> GetAll()
        {
            var list = new List<T>();
            using (var conn = new SqlConnection(_connStr))
            {
                conn.Open();
                string sql = $"SELECT * FROM {_tableName}";
                using (var cmd = new SqlCommand(sql, conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        T entity = new T();
                        foreach (var prop in _properties)
                        {
                            if (!reader.HasColumn(prop.Name) || reader[prop.Name] is DBNull) continue;
                            var value = reader[prop.Name];
                            // 类型不一致时自动转换
                            if (value != null && prop.PropertyType != value.GetType())
                            {
                                value = Convert.ChangeType(value, prop.PropertyType);
                            }
                            prop.SetValue(entity, value);
                        }
                        list.Add(entity);
                    }
                }
            }
            return list;
        }
    }

    /// <summary>
    /// 扩展方法，用于检查 IDataRecord 是否包含指定列
    /// </summary>
    public static class DataReaderExtensions
    {
        public static bool HasColumn(this IDataRecord reader, string columnName)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                if (reader.GetName(i).Equals(columnName, StringComparison.OrdinalIgnoreCase))
                    return true;
            }
            return false;
        }
    }
}
